<?php
//フリーワード検索
require_once '../db_connect.php';
	$search_volunteer_sql = '
		select distinct volunteer_information_id,volunteer_name,content
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where (Voinf.volunteer_name like ?
		or Voinf.content like ?
		or Voinf.target_gender like ?
		or Voinf.location like ?
		or Voinf.access like ?
		or Voinf.pref like ?
		or sCate.sub_category_name like ?
		or mCate.main_category_name like ?)
		and current_date() <= Voinf.end_time
	';

	$search_volunteer_count_sql = '
		select distinct count(volunteer_information_id)
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where (Voinf.volunteer_name like ?
		or Voinf.content like ?
		or Voinf.target_gender like ?
		or Voinf.location like ?
		or Voinf.access like ?
		or Voinf.pref like ?
		or sCate.sub_category_name like ?
		or mCate.main_category_name like ?)
		and current_date() <= Voinf.end_time
	';

	$volunteer_and_sql = '
		 and (Voinf.volunteer_name like ?
		or Voinf.content like ?
		or Voinf.target_gender like ?
		or Voinf.location like ?
		or Voinf.access like ?
		or Voinf.pref like ?
		or sCate.sub_category_name like ?
		or mCate.main_category_name like ?)
		and current_date() <= Voinf.end_time
	';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	$search_thread_sql = '
		select *
		from thread
		where thread_name like ?
	';

	$search_thread_count_sql = '
		select count(*)
		from thread
		where thread_name like ?
	';

	$thread_and_sql = ' and thread_name like ?';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

	$get_word 		= "";			//フリーワード検索の検索ワードを格納する変数
	$search_words 	= array();		//複数キーワード検索に対応するための変数
	$targetpage 	= "volunteer";	//検索を行う範囲のターゲットを指定するための変数
	$count_array 	= 0;			//検索ワードの個数を格納する変数
	$page 			= 0;			//現在のページ
	$max_limit 		= 10;			//最大10件表示
	$start_limit 	= 0;			//表示を開始する行数
//フリーワード検索準備開始
	if (!empty($_POST['search_word']) || !empty($_GET['page']) || !empty($_GET['page_free']))
	{
		if (!empty($_POST['search_word']))//検索ボックスから入力あったら・・・
		{
			$get_word = htmlspecialchars($_POST['search_word'],ENT_QUOTES,"UTF-8");//検索ボックスからの入力値をチェック
			$get_word = mb_convert_kana($get_word,"s");//全角空白を半角空白に変換

			if (strpos($get_word," ") === 0)//文字列の先頭が空白なら・・・
			{
				while (strpos($get_word," ") === 0)//最初の文字列が出現するまでの空白をすべて削除
				{
					$get_word = ltrim($get_word);
				}
			}
			else if (strrpos($get_word," ") === mb_strlen($get_word)-1)//文字列の最後が空白なら・・・
			{
				while (strrpos($get_word," ") === mb_strlen($get_word)-1)//最後の文字が出現するまでの空白をすべて削除
				{
					$get_word = rtrim($get_word);
				}
			}
		}
		else if (!empty($_GET['page_free']))//ページ切り替えしたら・・・
		{
			$get_word = htmlspecialchars($_GET['page_free'],ENT_QUOTES,"UTF-8");//GETで飛んできた値をチェック
			$get_word = mb_convert_kana($get_word,"s");//全角空白を半角空白に変換
			$start_limit = $max_limit * $_GET['page'] - 10;//表示を開始する行数を設定
		}

		$search_words = preg_split("/[\s]/",$get_word);//文字列を空白で分割

		$count_array  = count($search_words)-1;
//フリーワード検索　ターゲット指定（ボランティア：掲示板）sql文の生成
		if (!empty($_GET['target']))
		{
			$targetpage = htmlspecialchars($_GET['target'],ENT_QUOTES,"UTF-8");
			switch ($targetpage)
			{
				case "volunteer":
//フリーワード検索　検索対象文字列を配列で生成（ボランティア）
					if ($count_array > 0)
					{
						for ($i = 0; $i < $count_array; $i++)
						{
							$search_volunteer_sql = $search_volunteer_sql.$volunteer_and_sql;
							$search_volunteer_count_sql = $search_volunteer_count_sql.$volunteer_and_sql;
						}
					}
					$search_volunteer_sql.=" order by updata_date desc limit ?,?";

					$target_volunteer_array = array();//検索対象の文字列を格納する配列

					$volunteer_word_count = 0;//for文で使うカウント用の変数
					for ($i = 0; $i <= $count_array; $i++)
					{
						for ($y = 0; $y <= 7; $y++)
						{
							$target_volunteer_array[$volunteer_word_count] = "%".$search_words[$i]."%";
							$volunteer_word_count += 1;
						}
					}
					$volunteer_count_array = $target_volunteer_array;//全件検索用の値を退避
					array_push($target_volunteer_array,$start_limit);//limit 表示開始行の設定
					array_push($target_volunteer_array,$max_limit);//limit 最大表示件数の設定
				break;
				case "thread":
//フリーワード検索　検索対象文字列を配列で生成（掲示板）
					if ($count_array > 0)
					{
						for ($i = 0; $i < $count_array; $i++)
						{
							$search_thread_sql = $search_thread_sql.$thread_and_sql;
							$search_thread_count_sql = $search_thread_count_sql.$thread_and_sql;
						}
					}
					$search_thread_sql.=" order by thread_date desc limit ?,?";

					$target_thread_array = array();//検索対象の文字列を格納する配列

					$thread_word_count = 0;//for文で使うカウント用の変数
					for ($i = 0; $i <= $count_array; $i++)
					{
						$target_thread_array[$thread_word_count] = "%".$search_words[$i]."%";
					}
					$thread_count_array = $target_thread_array;//全件検索用の値を退避
					array_push($target_thread_array,$start_limit);//limit 表示開始行の設定
					array_push($target_thread_array,$max_limit);//limit 最大表示件数の設定
				break;
			}
		}
		else
		{
			if ($count_array > 0)
			{
				for ($i = 0; $i < $count_array; $i++)
				{
					$search_volunteer_sql = $search_volunteer_sql.$volunteer_and_sql;
					$search_volunteer_count_sql = $search_volunteer_count_sql.$volunteer_and_sql;
				}
			}
			$search_volunteer_sql.=" order by updata_date desc limit ?,?";
//フリーワード検索　検索対象文字列を配列で生成（ボランティア）
			$target_volunteer_array = array();//検索対象の文字列を格納する配列

			$volunteer_word_count = 0;//for文で使うカウント用の変数
			for ($i = 0; $i <= $count_array; $i++)
			{
				for ($y = 0; $y <= 7; $y++)
				{
					$target_volunteer_array[$volunteer_word_count] = "%".$search_words[$i]."%";
					$volunteer_word_count += 1;
				}
			}
			$volunteer_count_array = $target_volunteer_array;//全件検索用の値を退避
			array_push($target_volunteer_array,$start_limit);//limit 表示開始行の設定
			array_push($target_volunteer_array,$max_limit);//limit 最大表示件数の設定
		}
//フリーワード検索　sql文の実行・結果表示
		if(!empty($get_word))
		{
			switch ($targetpage)
			{
				case "volunteer":
					$sth = $db->prepare($search_volunteer_count_sql);
					$sth = $db->execute($sth,$volunteer_count_array);
					echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

					$sth = $db->prepare($search_volunteer_sql);
					$sth = $db->execute($sth,$target_volunteer_array);

					while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
					{
						echo "<table id='table-02'><tr><th width='120px'>ボランティア名</th><a href='../volunteer_function/display_volunteer_information.php?volunteer_information_id=".$row['volunteer_information_id']."'><td valign='middle'>".$row['volunteer_name']."</td></a></tr><tr><th>内容</th><td>".$row['content']."</td></tr></tabel>";
					}

					$sth = $db->prepare($search_volunteer_count_sql);
					$sth = $db->execute($sth,$volunteer_count_array);
					$sth = ceil(array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))/10);

					echo "<center><table><tr>";
					for ($i = 1; $i <= $sth; $i++)
					{
						echo "<td><a href='search_display_free.php?page=".$i."&page_free=".$get_word."&target=".$targetpage."'/>".$i."</td>";
					}
					echo "</tr></table></center>";
					break;
				case "thread":
					$sth = $db->prepare($search_thread_count_sql);
					$sth = $db->execute($sth,$thread_count_array);
					echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

					$sth = $db->prepare($search_thread_sql);
					$sth = $db->execute($sth,$target_thread_array);

					while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
					{
						echo "<table id='table-02'><tr><th width='120px'>スレッド名</th><td>".$row['thread_name']."</td></tr><tr><th>最新</th><td>".$row['thread_date']."</tr></tabel>";
					}

					$sth = $db->prepare($search_thread_count_sql);
					$sth = $db->execute($sth,$thread_count_array);
					$sth = ceil(array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))/10);

					echo "<center><table><tr>";
					for ($i = 1; $i <= $sth; $i++)
					{
						echo "<td><a href='search_display_free.php?page=".$i."&page_free=".$get_word."&target=".$targetpage."'/>".$i."</td>";
					}
					echo "</tr></table></center>";
					break;
				default:
					$sth = $db->prepare($search_volunteer_count_sql);
					$sth = $db->execute($sth,$target_volunteer_array);
					echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

					$sth = $db->prepare($search_volunteer_sql);
					$sth = $db->execute($sth,$target_volunteer_array);

					while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
					{
						echo "<table id='table-02'><tr><th width='120px'>ボランティア名</th><a href='../volunteer_function/display_volunteer_information.php?volunteer_information_id=".$row['volunteer_information_id']."'><td valign='middle'>".$row['volunteer_name']."</td></a></tr><tr><th>内容</th><td>".$row['content']."</td></tr></tabel>";
					}

					$sth = $db->prepare($search_volunteer_count_sql);
					$sth = $db->execute($sth,$target_volunteer_array);
					$sth = ceil(array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))/10);

					echo "<center><table><tr>";
					for ($i = 1; $i <= $sth; $i++)
					{
						echo "<td><a href='search_display_free.php?page=".$i."&page_free=".$get_word."&target=".$targetpage."'/>".$i."</td>";
					}
					echo "</tr></table></center>";
					break;
			}
		}
	}